package org.csu.onlineedubackend.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.csu.onlineedubackend.dto.PendingAssignmentDTO;
import org.csu.onlineedubackend.dto.StudentCountDTO;
import org.csu.onlineedubackend.entity.StudentAnswer;

import java.util.List;

@Mapper
public interface StudentAnswerMapper extends BaseMapper<StudentAnswer> {
    // 如果有自定义SQL，可以在这里加方法
    // 获得学生回答问题数
    @Select("SELECT COUNT(*) FROM student_answer WHERE student_id = #{studentId}")
    int countByStudentId(@Param("studentId") Integer studentId);


    // 1. 批量统计所有学生解题数
    @Select("""
    SELECT student_id, COUNT(*) AS count
    FROM student_answer
    GROUP BY student_id
""")
    List<StudentCountDTO> countAnswerByStudentIds();
    
    /**
     * 统计回答指定问题的学生数量（去重）
     * @param questionId 问题ID
     * @return 回答该问题的学生数量
     */
    @Select("SELECT COUNT(DISTINCT student_id) FROM student_answer WHERE question_id = #{questionId}")
    int countDistinctStudentsByQuestionId(@Param("questionId") Integer questionId);

    /**
     * 檢查學生是否完成了特定作業
     * @param userId 學生ID
     * @param questionId 作業ID
     * @return 完成次數（0表示未完成）
     */
    @Select("SELECT COUNT(*) FROM student_answer WHERE student_id = #{userId} AND question_id = #{questionId}")
    Integer countByUserIdAndQuestionId(@Param("userId") Integer userId, @Param("questionId") Integer questionId);

    // 查询教师名下所有课程中未批改（分数为null）的学生作业
    @Select("""
        SELECT sa.id AS answerId, sa.student_id AS studentId, sa.question_id AS questionId, sa.answered_at AS answeredAt, sa.answer,sa.score AS answerScore,
               q.title AS questionTitle, q.course_id AS courseId, c.title AS courseName, s.name AS studentName
        FROM student_answer sa
        JOIN question q ON sa.question_id = q.id
        JOIN course c ON q.course_id = c.id
        JOIN user s ON sa.student_id = s.id
        WHERE c.teacher_id = #{teacherId}
          AND sa.score IS NULL
        ORDER BY sa.answered_at DESC
        """)
    List<PendingAssignmentDTO> selectPendingAssignmentsByTeacher(@Param("teacherId") Integer teacherId);
}
